/**
 * 课程相关路由
 */

const express = require('express');
const router = express.Router();
const { db } = require('../db');
const { authenticateToken } = require('./auth');

// 中间件：检查课程访问权限
const checkCourseAccess = (req, res, next) => {
  const { role, teacher_id, student_id } = req.user;
  const courseId = parseInt(req.params.id);

  // 管理员可以访问所有课程
  if (role === 'admin') {
    return next();
  }

  // 教师可以访问自己的课程
  if (role === 'teacher') {
    db.get(
      'SELECT 1 FROM courses WHERE id = ? AND teacher_id = ?',
      [courseId, teacher_id],
      (err, result) => {
        if (err) {
          return res.status(500).json({ error: '数据库查询错误' });
        }
        if (result) {
          return next();
        }
        // 允许教师查看其他课程的基本信息
        if (req.method === 'GET') {
          return next();
        }
        res.status(403).json({ error: '无权访问该课程' });
      }
    );
    return;
  }

  // 学生可以查看所有课程信息，但只能操作自己已选的课程
  if (role === 'student') {
    if (req.method === 'GET') {
      return next();
    }
    db.get(
      'SELECT 1 FROM enrollments WHERE course_id = ? AND student_id = ?',
      [courseId, student_id],
      (err, result) => {
        if (err) {
          return res.status(500).json({ error: '数据库查询错误' });
        }
        if (result) {
          return next();
        }
        res.status(403).json({ error: '无权操作该课程' });
      }
    );
    return;
  }

  res.status(403).json({ error: '无权访问' });
};

// 获取课程列表
router.get('/', authenticateToken, (req, res) => {
  const { semester, department, status } = req.query;
  const { role, student_id, teacher_id } = req.user;

  let query = `
    SELECT 
      c.id, c.course_id, c.name, c.credit, c.hours,
      c.description, c.max_students, c.semester, c.status,
      t.name as teacher_name, t.title as teacher_title,
      (SELECT COUNT(*) FROM enrollments WHERE course_id = c.id) as enrolled_count
    FROM courses c
    JOIN teachers t ON c.teacher_id = t.id
    WHERE 1=1
  `;
  const params = [];

  // 添加查询条件
  if (semester) {
    query += ' AND c.semester = ?';
    params.push(semester);
  }
  if (department) {
    query += ' AND t.department = ?';
    params.push(department);
  }
  if (status) {
    query += ' AND c.status = ?';
    params.push(status);
  }

  // 根据角色筛选
  if (role === 'teacher' && req.query.my === 'true') {
    query += ' AND c.teacher_id = ?';
    params.push(teacher_id);
  } else if (role === 'student' && req.query.my === 'true') {
    query = `
      SELECT 
        c.id, c.course_id, c.name, c.credit, c.hours,
        c.description, c.max_students, c.semester, c.status,
        t.name as teacher_name, t.title as teacher_title,
        e.status as enrollment_status, e.grade,
        (SELECT COUNT(*) FROM enrollments WHERE course_id = c.id) as enrolled_count
      FROM courses c
      JOIN teachers t ON c.teacher_id = t.id
      JOIN enrollments e ON c.id = e.course_id
      WHERE e.student_id = ?
    `;
    params.push(student_id);

    if (semester) {
      query += ' AND c.semester = ?';
      params.push(semester);
    }
  }

  query += ' ORDER BY c.course_id';

  db.all(query, params, (err, courses) => {
    if (err) {
      return res.status(500).json({ error: '获取课程列表失败', details: err.message });
    }
    res.json({ courses });
  });
});

// 获取单个课程详情
router.get('/:id', authenticateToken, checkCourseAccess, (req, res) => {
  const courseId = req.params.id;
  const { role, student_id } = req.user;

  let query = `
    SELECT 
      c.*, t.name as teacher_name, t.title as teacher_title,
      (SELECT COUNT(*) FROM enrollments WHERE course_id = c.id) as enrolled_count
  `;

  if (role === 'student') {
    query += `, 
      (SELECT status FROM enrollments WHERE course_id = c.id AND student_id = ?) as enrollment_status,
      (SELECT grade FROM enrollments WHERE course_id = c.id AND student_id = ?) as grade
    `;
  }

  query += `
    FROM courses c
    JOIN teachers t ON c.teacher_id = t.id
    WHERE c.id = ?
  `;

  const params = role === 'student' ? [student_id, student_id, courseId] : [courseId];

  db.get(query, params, (err, course) => {
    if (err) {
      return res.status(500).json({ error: '获取课程详情失败', details: err.message });
    }
    if (!course) {
      return res.status(404).json({ error: '课程不存在' });
    }

    // 获取课程时间表
    db.all(
      `SELECT * FROM course_schedules WHERE course_id = ? ORDER BY day_of_week, start_time`,
      [courseId],
      (err, schedules) => {
        if (err) {
          return res.status(500).json({ error: '获取课程时间表失败', details: err.message });
        }
        course.schedules = schedules;
        res.json({ course });
      }
    );
  });
});

// 创建新课程（仅管理员和教师）
router.post('/', authenticateToken, (req, res) => {
  const { role, teacher_id } = req.user;
  if (role !== 'admin' && role !== 'teacher') {
    return res.status(403).json({ error: '无权创建课程' });
  }

  const {
    course_id,
    name,
    credit,
    hours,
    description,
    max_students,
    semester,
    teacher_id: assignedTeacherId,
    schedules
  } = req.body;

  // 验证必需字段
  if (!course_id || !name || !credit || !hours || !semester) {
    return res.status(400).json({ error: '缺少必需字段' });
  }

  // 如果是教师，只能创建自己的课程
  const finalTeacherId = role === 'teacher' ? teacher_id : assignedTeacherId;
  if (!finalTeacherId) {
    return res.status(400).json({ error: '未指定教师' });
  }

  // 开始事务
  db.serialize(() => {
    db.run('BEGIN TRANSACTION');

    // 创建课程
    db.run(
      `INSERT INTO courses (
        course_id, name, credit, hours, description,
        max_students, teacher_id, semester, status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'open')`,
      [course_id, name, credit, hours, description, max_students, finalTeacherId, semester],
      function(err) {
        if (err) {
          db.run('ROLLBACK');
          return res.status(500).json({ error: '创建课程失败', details: err.message });
        }

        const courseId = this.lastID;

        // 如果提供了课程时间表，则创建时间表
        if (schedules && schedules.length > 0) {
          let schedulesCreated = 0;
          let scheduleError = false;

          schedules.forEach(schedule => {
            db.run(
              `INSERT INTO course_schedules (
                course_id, day_of_week, start_time, end_time, classroom
              ) VALUES (?, ?, ?, ?, ?)`,
              [courseId, schedule.day_of_week, schedule.start_time, schedule.end_time, schedule.classroom],
              (err) => {
                schedulesCreated++;
                if (err) {
                  scheduleError = true;
                }

                // 当所有时间表都处理完成时
                if (schedulesCreated === schedules.length) {
                  if (scheduleError) {
                    db.run('ROLLBACK');
                    return res.status(500).json({ error: '创建课程时间表失败' });
                  }

                  db.run('COMMIT', (err) => {
                    if (err) {
                      return res.status(500).json({ error: '提交事务失败' });
                    }
                    res.json({
                      message: '课程创建成功',
                      courseId: courseId
                    });
                  });
                }
              }
            );
          });
        } else {
          db.run('COMMIT', (err) => {
            if (err) {
              return res.status(500).json({ error: '提交事务失败' });
            }
            res.json({
              message: '课程创建成功',
              courseId: courseId
            });
          });
        }
      }
    );
  });
});

// 更新课程信息（仅课程教师和管理员）
router.put('/:id', authenticateToken, checkCourseAccess, (req, res) => {
  const courseId = req.params.id;
  const { role, teacher_id } = req.user;
  const {
    name,
    credit,
    hours,
    description,
    max_students,
    status,
    schedules
  } = req.body;

  // 验证是否为课程教师或管理员
  db.get(
    'SELECT teacher_id FROM courses WHERE id = ?',
    [courseId],
    (err, course) => {
      if (err) {
        return res.status(500).json({ error: '数据库查询错误' });
      }
      if (!course) {
        return res.status(404).json({ error: '课程不存在' });
      }
      if (role !== 'admin' && course.teacher_id !== teacher_id) {
        return res.status(403).json({ error: '无权更新该课程' });
      }

      // 开始事务
      db.serialize(() => {
        db.run('BEGIN TRANSACTION');

        // 更新课程基本信息
        db.run(
          `UPDATE courses 
           SET name = ?, credit = ?, hours = ?, description = ?,
               max_students = ?, status = ?
           WHERE id = ?`,
          [name, credit, hours, description, max_students, status, courseId],
          (err) => {
            if (err) {
              db.run('ROLLBACK');
              return res.status(500).json({ error: '更新课程失败', details: err.message });
            }

            // 如果提供了新的时间表，则更新时间表
            if (schedules) {
              // 先删除原有时间表
              db.run(
                'DELETE FROM course_schedules WHERE course_id = ?',
                [courseId],
                (err) => {
                  if (err) {
                    db.run('ROLLBACK');
                    return res.status(500).json({ error: '删除原时间表失败' });
                  }

                  // 插入新时间表
                  let schedulesCreated = 0;
                  let scheduleError = false;

                  schedules.forEach(schedule => {
                    db.run(
                      `INSERT INTO course_schedules (
                        course_id, day_of_week, start_time, end_time, classroom
                      ) VALUES (?, ?, ?, ?, ?)`,
                      [courseId, schedule.day_of_week, schedule.start_time, schedule.end_time, schedule.classroom],
                      (err) => {
                        schedulesCreated++;
                        if (err) {
                          scheduleError = true;
                        }

                        // 当所有时间表都处理完成时
                        if (schedulesCreated === schedules.length) {
                          if (scheduleError) {
                            db.run('ROLLBACK');
                            return res.status(500).json({ error: '创建新时间表失败' });
                          }

                          db.run('COMMIT', (err) => {
                            if (err) {
                              return res.status(500).json({ error: '提交事务失败' });
                            }
                            res.json({ message: '课程更新成功' });
                          });
                        }
                      }
                    );
                  });
                }
              );
            } else {
              db.run('COMMIT', (err) => {
                if (err) {
                  return res.status(500).json({ error: '提交事务失败' });
                }
                res.json({ message: '课程更新成功' });
              });
            }
          }
        );
      });
    }
  );
});

// 获取课程的选课学生列表
router.get('/:id/students', authenticateToken, checkCourseAccess, (req, res) => {
  const courseId = req.params.id;

  db.all(
    `SELECT 
      s.id, s.student_id, s.name, s.gender, s.class,
      e.enrollment_date, e.grade, e.status
    FROM enrollments e
    JOIN students s ON e.student_id = s.id
    WHERE e.course_id = ?
    ORDER BY s.student_id`,
    [courseId],
    (err, students) => {
      if (err) {
        return res.status(500).json({ error: '获取学生列表失败', details: err.message });
      }
      res.json({ students });
    }
  );
});

module.exports = router;
